>I want to do a select which only returns the highest revision
>level for each filename, so the select will return two rows,
>the first filename with revision = "3" and the second filename
>with revision set to "1".
Try
SELECT filename,max(revision) FROM <table>
GROUP BY filename;
You can add a WHERE clause before the GROUP BY to only look at certain
filenames (e.g. WHERE filename~'foo') or if you're looking for files with a
specific latest revision number (e.g. WHERE revision=3) and an ORDER BY
after the GROUP BY (e.g. ORDER BY filename).
HTH,
S.
+--------------------------+--------------------------------------+
| Stuart C. G. Rison | Ludwig Institute for Cancer Research |
+--------------------------+ 91 Riding House Street |
| N.B. new phone code!! | London, W1P 8BT |
| Tel. +44 (0)207 878 4041 | UNITED KINGDOM |
| Fax. +44 (0)207 878 4040 | stuart@ludwig.ucl.ac.uk |
+--------------------------+--------------------------------------+